﻿using DAL;
using System.Data;
using System.Data.SqlClient;
using System.Text;



namespace DLL
{
    public class LogDal
    {
        private SQLHelper sqlhelper = new SQLHelper();
        public bool addLogInfo(Model.LogEntity logInfo)
        {

            StringBuilder sbSql = new StringBuilder();
            string sql = " INSERT INTO LogEntity(studentId,pagerType,excelInfo,wordInfo,pptInfo,winInfo,ieInfo,timeSpan,sumErrerInfo,uploadInfo,errerInfo,userIp)  values(@studentId,@pagerType,@excelInfo,@wordInfo,@pptInfo,@winInfo,@ieInfo,@timeSpan,@sumErrerInfo,@uploadInfo,@errerInfo,@userIp)";

            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("studentId",logInfo.studentId.ToString() ),
                new  SqlParameter("pagerType",logInfo.pagerType.ToString() ),
                new  SqlParameter("excelInfo",logInfo.excelInfo.ToString() ),
                new  SqlParameter("wordInfo",logInfo.wordInfo.ToString()),
                new  SqlParameter("pptInfo",logInfo.pptInfo.ToString() ),
                new  SqlParameter("winInfo",logInfo.winInfo.ToString() ),
                new  SqlParameter("ieInfo",logInfo.ieInfo.ToString() ),
                new  SqlParameter("timeSpan",logInfo.timeSpan.ToString() ),
                new  SqlParameter("sumErrerInfo",logInfo.sumErrerInfo.ToString() ),
                new  SqlParameter("uploadInfo",logInfo.uploadInfo.ToString() ),
                new  SqlParameter("errerInfo",logInfo.errerInfo.ToString() ),
                new  SqlParameter("userIp",logInfo.userIp.ToString() )
            };

            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            if (flag > 0)
            {

                return true;
            }
            else
            {
                return false;
            }


        }

        public DataTable selectLogInfo(string startTime, string endTime, string ip)
        {
            StringBuilder sbSql = new StringBuilder();
            //string sql = "select * from LogEntity where userIp like '" + ip + "%' and timeSpan BETWEEN CONVERT(varchar(15),'" + startTime + "',120) and CONVERT(varchar(15),'" + endTime + "',120)";
            string sql = "select * from LogEntity where userIp like '" + ip + "%' and timeSpan BETWEEN CONVERT(varchar(15),'" + startTime + "',120) and CONVERT(varchar(15),'" + endTime + "',120) and (wordInfo != '' OR excelInfo != '' OR winInfo != '' OR pptInfo != '' OR ieInfo != '')";
            DataTable dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }

        public DataTable selectNoLogInfo(string startTime, string endTime)
        {
            StringBuilder sbSql = new StringBuilder();
            //string sql = "select * from LogEntity where userIp like '" + ip + "%' and timeSpan BETWEEN CONVERT(varchar(15),'" + startTime + "',120) and CONVERT(varchar(15),'" + endTime + "',120)";
            string sql = "select * from LogEntity where timeSpan BETWEEN CONVERT(varchar(15),'" + startTime + "',120) and CONVERT(varchar(15),'" + endTime + "',120) and (wordInfo != '' OR excelInfo != '' OR winInfo != '' OR pptInfo != '' OR ieInfo != '')";
            DataTable dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        /// <summary>
        /// 查询SetUp表——姜晶——2018年3月26日
        /// </summary>
        /// <returns></returns>
        public DataTable QuerySetUpDal()
        {
            string sql = "select*from SetUp";
            DataTable dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
    }
}
